package com.sh.demo.qa.util;

import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.dom4j.io.SAXReader;
import org.dom4j.Document;
import org.dom4j.Element;

public class ExcelUtil {

	/**
	 * EXCEL的导出(无连接数据库)
	 * 
	 * @param response response
	 * @param fileName 输出的文件的名字，比如 excel.xml
	 * @param dataList 存放 javaBean 的list
	 * @param cls javaBean的类型
	 * @param xmlFile XML配置文件
	 * @param eleName 信息节点名称，默认filed
	 * @throws Exception
	 */
	public static <T> void write(OutputStream os, List<T> dataList, Class<T> cls, File xmlFile, String eleName)
	        throws Exception {

		// response.setContentType("application/octet-stream");
		// response.setHeader("Content-disposition", "attachment;filename=" +
		// new String(fileName.getBytes("GBK"), "ISO-8859-1"));
		// OutputStream os = response.getOutputStream();

		int sheetCount = dataList.size() / 59999; // 要创建几个sheet页
		if (dataList.size() % 59999 > 0) {
			sheetCount += 1;
		}

		// 初始化变量
		WritableWorkbook book = null;
		// WritableSheet sheet = null;
		WritableSheet[] sheet = new WritableSheet[sheetCount];
		Label label = null;
		List<List<String>> config = null;
		List<Object> title = null;
		List<Object> propery = null;

		// 创建可写入的工作簿对象
		book = Workbook.createWorkbook(os);
		// 在工作簿里创建可写入的工作表，第一个参数为工作表名，第二个参数为该工作表的所在位置
		for (int i = 0; i < sheet.length; i++) {
			sheet[i] = book.createSheet("工作簿" + i, i);
		}

		// 获得对应配置文件
		config = readTempletConfig(xmlFile, eleName);
		title = new ArrayList<Object>();
		propery = new ArrayList<Object>();

		// 生成对应的配置
		for (int i = 0; i < config.size(); i++) {
			List<String> element = config.get(i);
			title.add(element.get(0));
			propery.add(element.get(1));
		}

		// 给每个sheet写标题行
		for (int j = 0; j < sheet.length; j++) {
			for (int i = 0; i < title.size(); i++) {
				label = new Label(i, 0, (String) title.get(i));
				sheet[j].addCell(label);
			}
		}
		// 写入数据
		int tmp = 0; // sheet页
		if (dataList != null && dataList.size() != 0) {
			for (int i = 0; i < dataList.size(); i++) {
				for (int j = 0; j < propery.size(); j++) {
					String properyName = (String) propery.get(j);
					Method getMethod = BeanUtil.getGetMethod(properyName, cls);
					if (getMethod != null) {
						Object obj = getMethod.invoke(dataList.get(i), new Object[] {});
						Class<?> typeClass = getMethod.getReturnType(); // 获取返回方法的类型
						String cellValue = BeanUtil.getString(typeClass, obj);
						int cell = 0; // 列
						cell = i % 59999;
						if (cell == 0) {
							cell = 1;
						} else {
							cell++;
						}
						label = new Label(j, cell, cellValue);
						sheet[tmp].addCell(label);
						if (i != 0 && i % 59999 == 0 && j == 0) { // 如果写满一个sheet，就换下一个sheet
							tmp++;
						}
					}
				}
			}
			// 如果数据量过大导致出错，可以在这行加代码，做一个判断：如果i=1000的时候， book.write();
		}
		// 从内存中写入到客户端
		book.write();
		try {
			if (book != null) {
				book.close();
				book = null;
			}
		} catch (Exception e) {

		}
	}

	/**
	 * 解析上传的excel文件，返回list，list中存放对象
	 * 
	 * @param request
	 * @param cls javaBean对象类型
	 * @param xmlFile XML配置文件的模板
	 * @param eleName 信息节点名称，默认filed
	 * @return list
	 * @throws Exception
	 */
	public static <T> List<T> read(InputStream is, Class<T> cls, File xmlFile, String eleName) throws Exception {
		// FileItem item = (FileItem)
		// request.getAttribute(ActionRequest.FILE_ITEM);
		// 初始化变量
		Workbook book = null;
		List<T> beanList = null;
		Map<Integer, String> setProperties = null;
		Map<String, String> config = null;

		// 生成Bean的列表
		beanList = new ArrayList<T>();
		// Bean属性方法的列表
		setProperties = new HashMap<Integer, String>();
		// 获取工作簿对象
		book = Workbook.getWorkbook(is);
		Sheet[] sheets = book.getSheets(); // 如果为空，直接抛出空指针异常
		// 获得对应配置文件
		config = readTempletConfig(eleName, xmlFile);

		// 获得字段名并生成属性方法的列表
		Cell[] propertyCells = sheets[0].getRow(0);
		for (int i = 0; i < propertyCells.length; i++) {
			String name = propertyCells[i].getContents();
			Object obj = config.get(name);
			if (obj != null) {
				String code = (String) obj;
				setProperties.put(new Integer(i), code);
			}
		}

		// 将记录设置到List中
		for (int tp = 0; tp < sheets.length; tp++) {
			int rows = sheets[tp].getRows();
			for (int i = 1; i < rows; i++) {
				Cell[] cells = sheets[tp].getRow(i);
				T obj = cls.newInstance();
				for (int j = 0; j < cells.length; j++) {
					String perpertyName = (String) setProperties.get(new Integer(j));
					Method setMethod = BeanUtil.getSetMethod(perpertyName, cls);
					if (setMethod != null) {
						Object value[] = new Object[1];
						String cellValue = cells[j].getContents();
						Method getMethod = BeanUtil.getGetMethod(perpertyName, cls);
						Class<?> perpertyClass = getMethod.getReturnType();
						value[0] = BeanUtil.getObject(perpertyClass, cellValue);
						// 执行set方法
						setMethod.invoke(obj, value);
					}
				}
				beanList.add(obj);
			}
		}
		return beanList;
	}

	/**
	 * 读取Excel模版的配置信息(用于XML的下载)
	 * 
	 * @param file XML配置文件
	 * @param eleName 信息节点名称，默认filed
	 */
	@SuppressWarnings("unchecked")
	private static List<List<String>> readTempletConfig(File file, String eleName) throws Exception {
		List<List<String>> config = new ArrayList<List<String>>();
		SAXReader reader = new SAXReader();
		Document doc = reader.read(file);
		Element root = doc.getRootElement();
		Element foo;
		for (Iterator<Element> it = root.elementIterator(eleName); it.hasNext();) {
			List<String> element = new ArrayList<String>();
			foo = (Element) it.next();
			String name = foo.elementText("name");
			String code = foo.elementText("code");
			element.add(name);
			element.add(code);
			config.add(element);
		}
		return config;
	}

	/**
	 * 读取Excel模板的配置信息(上传的XML文件解析)
	 * 
	 * @param eleName 跟节点名字
	 * @param xmlFile XML配置文件
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	private static Map<String, String> readTempletConfig(String eleName, File xmlFile) throws Exception {
		Map<String, String> config = new HashMap<String, String>();
		SAXReader reader = new SAXReader();
		Document doc = reader.read(xmlFile);
		Element root = doc.getRootElement();
		Element foo;
		for (Iterator<Element> i = root.elementIterator(eleName); i.hasNext();) {
			foo = i.next();
			String key = foo.elementText("name");
			String value = foo.elementText("code");
			config.put(key, value);
		}
		return config;
	}
}
